package com.biz.hunter.dao;

import com.biz.hunter.db.entity.*;
import com.biz.hunter.dbutil.BaseDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Repository
public class RecommendDAO {
    public static final int PAGE_SIZE = 100;
    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private static Logger logger = LoggerFactory.getLogger(RecommendDAO.class);
    static final String SQL_SAVE = "insert into tb_recommend(personid"
            + ",jobid,createby,createdate,status,reason, platformid, audioid,referee_type) values(?,?,?,?,?,?,?,?,?)";

    private static final String SQL_UPDATE = "update tb_recommend set status=?,updatedate=? where id =?";

    private static final String SQL_GET_ALL = "select * from tb_recommend";
    private static final String SQL_GET_ALL_COUNT = "select COUNT(*) from tb_recommend as r, tb_jobdesc as j where  r.jobId=j.id ";
    private static final String SQL_GET_ALL_WITH_LIMIT = "select r.*, name as jobName from tb_recommend as r, tb_jobdesc as j where r.jobId=j.id ";

    private static final String SQL_GETBYID = "select * from tb_recommend  where id = ?";
    private static final String SQL_DELETE = "delete from tb_recommend where id=?";

    private static final String SQL_GETCREATER = "select * from tb_recommend  where createby = ? order by jobid limit ?,? ";
    private static final String SQL_GET_PERSON = "select * from tb_recommend  where personid = ? order by jobid limit ?,?";

    private static final String SQL_GET_JOB_PERSON = "select * from tb_recommend  where jobid = ? and personid = ? and platformid=?";

    private static final String SQL_GET_RECOMMEND_POSITION = "SELECT distinct a.jobid AS jobid, b.name AS jobname, b.reward AS jobreward, c.name AS companyname, a.createby as createby ,b.location,d.name as 'cityName' " +
            "from tb_recommend a, tb_jobdesc b, tb_company c ,tb_city as d where  b.cityid=d.id and  a.createby = ? and a.referee_type=? and a.jobid=b.id and b.companyid=c.id and a.platformid=? limit ?,?";

    private static final String SQL_GET_RECOMMENDED_PERSON = "select a.id AS id, a.status AS status, b.name AS name," +
            "b.cityName,b.domainName,b.companyname,b.workage,b.roleName from tb_recommend a , tb_person b "
            + "where a.personid = b.id and a.jobid=? and a.createby =?  and a.referee_type=? and a.platformid=? limit ?,?";

    private static final String SQL_GET_RECOMMENDED_PERSON_2 =
            "select a.id AS id, a.status AS status, b.name AS name, "
                    + "b.companyname as companyname,  b.companyid as companyid, "
                    + "c.name as companyname2, d.name as positionname,b.cityId "
                    + "from tb_recommend a , tb_jobtitle d, tb_person b "
                    + "left join tb_company c on b.companyid= c.id "
                    + "where a.personid = b.id and d.id=b.roleid and a.jobid=? and a.createby =? and a.platformid=? limit ?,?";
    private static final String SQL_GET_RECOMMENDED_PERSON_3 =
            "SELECT r.id AS id, r.status AS status, p.name AS name," +
                    "p.companyname as companyname,  p.companyid as companyid, " +
                    "c.name as companyname2, j.name as positionname,p.cityId " +
                    " from tb_recommend r " +
                    "LEFT JOIN tb_jobdesc j on r. jobid = j.id " +
                    "LEFT JOIN tb_person p ON r.personid = p.id " +
                    "LEFT JOIN tb_company c on j.companyid = c.id " +
                    "WHERE r.jobid = ? " +
                    "AND r.personid = ? " +
                    "AND r.createby = ? " +
                    "AND r.platformid = ? " +
                    "LIMIT ?,?";

    private static final String SQL_GET_RECOMMEND_PERSON_S = "select a.id AS recommendid, a.status AS status, b.name AS jobname, b.reward AS jobreward, c.name AS companyname from tb_recommend a, tb_jobdesc b, tb_company c where a.personid = a.createby and a.createby = ? and a.jobid=b.id and b.companyid=c.id and a.platformid=? limit ?,?";

    private static final String SQL_UPDTE_RESOURCE = "UPDATE tb_recommend SET resid=?, resstatus=? WHERE id=?";

    private static final String SQL_UPDTE_RESOURCE_AUDIO = "UPDATE tb_recommend SET audioid=? WHERE id=?";

    private static final String SQL_GET_JOB_PHONE = "select * from tb_recommend a, tb_person b  where a.personid = b.id and a.jobid = ? and b.phone = ? ";

    private static final String SQL_GET_RECOMMEND_PERSON_BY_JOBID = "select a.id AS id, a.status AS status, b.name AS name,"
            + " b.companyname as companyname,  b.companyid as companyid, c.name as companyname2, d.name as positionname "
            + "from tb_recommend a , tb_jobtitle d, tb_person b "
            + "left join tb_company c on b.companyid=c.id where a.personid = b.id and d.id=b.roleid and a.jobid=? limit ?, ?";
    private static final String SQL_GET_RECOMMEND_PERSON_ALL =
            "select a.id ,a.jobid,b.id as personid,e.empid,a.status,b.domainName,b.name,b.openid,g.`name` as cityName,b.companyname,b.workage,b.phone,d.`name` as roleName,b.workage,f.`name`as positionname"
                    + " from tb_recommend a , tb_jobtitle d, tb_jobdesc f,tb_person b"
                    + " left join tb_company c on b.companyid=c.id "
                    + " left join tb_employee e on b.id=e.personid "
                    + "left join tb_city g on g.id = b.cityId"
                    + " where f.id=a.jobid and a.personid=b.id and d.id=b.roleid and f.createdby=? order by a.createdate DESC limit ?, ?";

    public int save(Recommendation recommend) {
        Long id = null;
        if (recommend == null) {
            logger.error("the input recommendation is null");
            return -1;
        }

        id = BaseDAO.save(SQL_SAVE, recommend.getPersonId(),
                recommend.getJobId(), recommend.getCreateby(),
                recommend.getCreateDate(), recommend.getStatus(),
                recommend.getReason(), recommend.getPlatformId(),
                recommend.getAudioid(), recommend.getRefereeType());
        if (id != null) {
            recommend.setId(id.intValue());
            return recommend.getId();
        }

        return -1;
    }

    public int delete(String recommendid) {
        if (StringUtils.isEmpty(recommendid)) {
            logger.error("please provide the recommendation id which will be removed.");
            return -1;
        }
        return BaseDAO.execute(SQL_DELETE, recommendid);
    }

    public List<Recommendation> getALL() {
        return BaseDAO.queryForList(Recommendation.class, SQL_GET_ALL);
    }

    public Long getCountOfAll(String name, Integer status) {
        String sql = SQL_GET_ALL_COUNT;
        if (null != status) {
            sql += " and r.status =" + status;
        }
        if (StringUtils.hasText(name)) {
            sql += " and name like '" + name + "'";
        }

        return (Long) BaseDAO.queryOneRowOneCol(sql);
    }

    public List<Recommendation> getAllWithLimit(String name, Integer status, long start, long length) {
        String sql = SQL_GET_ALL_WITH_LIMIT;
        if (StringUtils.hasText(name)) {
            sql += " and name like '" + name + "%'";
        }
        if (null != status) {
            sql += " and r.status =" + status + " ";
        }
        sql += " LIMIT ?, ?";
        return BaseDAO.queryForList(Recommendation.class, sql, start, length);
    }

    public int update(Recommendation rd) {
        return BaseDAO.execute(SQL_UPDATE, rd.getStatus(), df.format(new Date()), rd.getId());
    }

    public int updateStatus(int status, int recommendid) {
        return BaseDAO.execute(SQL_UPDATE, status, df.format(new Date()), recommendid);
    }

    public int updateRes(Recommendation rd) {
        return BaseDAO.execute(SQL_UPDTE_RESOURCE, rd.getResid(), rd.getResstatus(), rd.getId());
    }

    public int updateAudioRes(Recommendation rd) {
        return BaseDAO.execute(SQL_UPDTE_RESOURCE_AUDIO, rd.getAudioid(), rd.getId());
    }

    public Recommendation getById(String Id) {
        return BaseDAO.queryForObject(Recommendation.class, SQL_GETBYID, Id);
    }

    public List<Recommendation> getByCreater(int personId, int start) {
        return BaseDAO.queryForList(Recommendation.class, SQL_GETCREATER, personId, start, PAGE_SIZE);
    }

    public List<Recommendation> getByPerson(String personId, int start) {
        return BaseDAO.queryForList(Recommendation.class, SQL_GET_PERSON, personId, start, PAGE_SIZE);
    }

    public List<Recommendation> getByJobandPerson(long jobId, int personId, int platformid) {
        return BaseDAO.queryForList(Recommendation.class, SQL_GET_JOB_PERSON, jobId, personId, platformid);
    }

    public List<RecommendPosition> getRecommendPositionByCreater(int platformid, int personId, int refereeType, int start) {
        return BaseDAO.queryForList(RecommendPosition.class, SQL_GET_RECOMMEND_POSITION, personId, refereeType, platformid, start, PAGE_SIZE);
    }

    public List<RecommendedPerson> getRecommendedPersonByPositionAndCreater(int platformid, int personId, int refereeType, int jobId, int start) {
        return BaseDAO.queryForList(RecommendedPerson.class, SQL_GET_RECOMMENDED_PERSON, jobId, personId, refereeType, platformid, start, 100);
    }

    public List<RecommendedPerson> getRecommendedPersonByPositionAndCreater(int platformid, int personId, int refereeType, int jobId, int start, int length) {
        return BaseDAO.queryForList(RecommendedPerson.class, SQL_GET_RECOMMENDED_PERSON, jobId, personId, refereeType, platformid, start, length);
    }

    public List<RecommendedPerson> getRecommendedPersonByPositionAndCreater2(int platformid, int personId, long jobId, int start) {
        return BaseDAO.queryForList(RecommendedPerson.class, SQL_GET_RECOMMENDED_PERSON_2, jobId, personId, platformid, start, PAGE_SIZE);
    }

    public List<RecommendedPerson> getRecommendedPersons(int platformid, int createdBy, long jobId, int personId, int start) {
        return BaseDAO.queryForList(RecommendedPerson.class, SQL_GET_RECOMMENDED_PERSON_3, jobId, personId, createdBy, platformid, start, PAGE_SIZE);
    }

    public List<SelfRecommendPosition> getSelfRecommendPositionByCreater(int platformid, int personId, int start) {
        return BaseDAO.queryForList(SelfRecommendPosition.class, SQL_GET_RECOMMEND_PERSON_S, personId, platformid, start, PAGE_SIZE);
    }

    private static final String SQL_GET_JOB_PHONE_PLAT = "select * from tb_recommend a, tb_person b  where a.personid = b.id "
            + "and a.jobid = ? and b.phone = ? and a.platformid =? ";

    public List<Recommendation> getByJobAndPhone(long jobId, String phone, int platformid) {
        return BaseDAO.queryForList(Recommendation.class, SQL_GET_JOB_PHONE_PLAT, jobId, phone, platformid);
    }

    public List<Recommendation> getByJobAndPhone(long jobId, String phone) {
        return BaseDAO.queryForList(Recommendation.class, SQL_GET_JOB_PHONE, jobId, phone);
    }

    public List<RecommendedPerson> getRecommendedPersonByPosition(long jobId, int start, int length) {
        return BaseDAO.queryForList(RecommendedPerson.class, SQL_GET_RECOMMEND_PERSON_BY_JOBID, jobId, start, length);
    }

    public List<RecommendedPerson> getRecommendedPersonByPosition(long jobId, int start) {
        return BaseDAO.queryForList(RecommendedPerson.class, SQL_GET_RECOMMEND_PERSON_BY_JOBID, jobId, start, PAGE_SIZE);
    }

    public List<RecommendedPerson> getRecommendedPersonAll(long createid, int start) {
        return BaseDAO.queryForList(RecommendedPerson.class, SQL_GET_RECOMMEND_PERSON_ALL, createid, start, PAGE_SIZE);
    }

    public static final String SQL_GET_DIY = "select * from tb_recommend where personid = ? and jobid = ? and status = ? limit 0,1";

    public Recommendation getByStatusAndJobIdAndPersonId(int personId, int jobid, int status) {
        return BaseDAO.queryForObject(Recommendation.class, SQL_GET_DIY, personId, jobid, status);
    }

    public List<RecommendRecord> getRecordByid(Integer recommendid) {
        String sql ="select * from tb_recommend_record where recommendId = ?";
        return BaseDAO.queryForList(RecommendRecord.class,sql,recommendid);
    }

    public Integer updateRemark(String recommendid, String remark) {
        String sql ="update tb_recommend_record set remark = ? where recommendId = ?";
        return BaseDAO.execute(sql,remark,recommendid);
    }

    public List<Recommendation> getByCreateAndEmpAndJob(int createdby, Integer empId, Integer jobId) {
        String sql = "select * from tb_recommend where createby = ? and personid = ? and jobid = ?";
        return BaseDAO.queryForList(Recommendation.class,sql,createdby,empId,jobId);
    }
}
